package jsyf.oa.dao.email;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.email.EmailReceiverEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2017/12/19 0019.
 */
@Repository
public class EmailReceiverDao extends BaseDao {

    public EmailReceiverDao() {
        super.iBaseEntity = new EmailReceiverEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, EmailReceiverEntity.class);
    }

    public List<JSONObject> emailReport(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" readerCompanyId, \n");
        sql.append(" readerCompanyName, \n");
        sql.append(" readerDepartName, \n");
        sql.append(" readerRoleName, \n");
        sql.append(" readerId, \n");
        sql.append(" readerName, \n");
        sql.append(" COUNT(1) allCount, \n");
        sql.append(" SUM(reader = 1) readedCount, \n");
        sql.append(" SUM(reader = 0) unreadCount, \n");
        sql.append(" COUNT(replyTime) replyCount \n");
        sql.append(" FROM V_SYS_EMAIL_REPORT \n");
        sql.append(" where  1=1 \n");
        sql.append(condition);
        sql.append(" AND readerCompanyName!='滚滚向前' ");
        sql.append(" AND readerDelFlag=0 ");
        sql.append(" AND readerRoleName IN ('总经理','大区经理','区域支持','销售管理经理','服务总监','大客户总监','大客户总经理') ");
        sql.append(" AND userType=0 ");
        sql.append(" GROUP BY readerCompanyId,readerCompanyName,readerId,readerName \n");
        sql.append(" ORDER BY readerCompanySortNo,readerDepartSortNo,readerRoleSortNo,readerSortNo \n");
        return super.queryForJsonList(sql.toString(), params);
    }

    public List<JSONObject> emailReportForDay(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT \n" +
                " readerCompanyId, \n" +
                " readerCompanyName, \n" +
                " readerDepartName, \n" +
                " readerRoleName, \n" +
                " readerId, \n" +
                " readerName, \n" +
                " COUNT(1) allCount, \n" +
                " SUM(reader = 1) readedCount, \n" +
                " SUM(reader = 0) unreadCount, \n" +
                " COUNT(replyTime) replyCount \n" +
                " FROM V_SYS_EMAIL_REPORT \n" +
                " where  1=1 \n" +
//                " AND userType=0  \n" +
                "AND readerDelFlag=0 \n " +
                "AND readerRoleName IN ('总经理','大区经理','区域支持','销售管理经理','服务总监','大客户总监','大客户总经理') \n");
        sql.append(condition);
        sql.append(" ORDER BY readerCompanySortNo,readerDepartSortNo,readerRoleSortNo,readerSortNo \n");
        return super.queryForJsonList(sql.toString(), params);
    }

}
